package me.sdevil507.biz.fruit.repository;

import me.sdevil507.biz.fruit.po.*;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * @program: tips_back_end
 * @description: dao
 * @author: kzl
 * @create: 2020-06-23 17:17
 **/
public interface FruitRepository  extends JpaRepository<FruitPO, Long>, JpaSpecificationExecutor<FruitPO>, QuerydslPredicateExecutor<FruitPO> {

    /**
     * 逻辑删除
     *
     * @param id 成果id
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying(clearAutomatically = true)
    @Query(value = "update FruitPO a set a.isDel=true where a.id=:id")
    void logicDelete(@Param("id") Long id);

    @Query(nativeQuery = true, value = "select count(id) from biz_fruit a where name=?1 and  type=?2 and org_id=?3  and IF(ISNULL(?4), true, id !=?4 ) and is_del='0'")
    int findExistsByNameAndId(String name,String type,Long orgId,Long id);

    @Query(nativeQuery = true, value = "select count(id)  from biz_fruit a where name=?1 and  type=?2 and org_id=?3 and IF(ISNULL(?4), true, id !=?4 ) and IF(ISNULL(?5), true, issue_number=?5) and is_del='0'")
    int findExistsByNameAndID(String name,String type,Long orgId,Long id,String issueNumber );

    @Query(nativeQuery = true, value = "  select\n" +
            "        s.d_name as resultName,type,date_format(f.create_time, '%Y-%m') as mont,\n" +
            "        count(f.type) as num \n" +
            "    from\n" +
            "        biz_fruit f cross \n" +
            "       JOIN biz_sysdict s \n" +
            "    where\n" +
            "        (\n" +
            "            f.is_del = 0\n" +
            "        ) \n" +
            "        and (\n" +
            "            s.is_del = 0\n" +
            "        ) \n" +
            "        and f.type=s.d_value \n" +
            "        and s.type_key=\"FruitType\"\n" +
            "and f.create_time>date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y')"+
            "\t\t\t\t  group by\n" +
            "        f.type,date_format(f.create_time, '%Y-%m')")
    List<ResultPO> findCountByTypeAndMon();

    @Query(nativeQuery = true, value = "select count(id)  from biz_fruit a where name=?1 and  type=?2 and org_id=?3 and IF(ISNULL(?4), true, id !=?4 ) and IF(ISNULL(?5), true, awards=?5) and is_del='0'")
    int findExistsByAwards(String name,String type,Long orgId,Long id,String awards );

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultPercentPO(bs.dname, count(b.inclinationLevel)) \n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitPO a ON a.id = bor.fruitId " +
            "	left JOIN FruitLeaderPO b ON a.id = b.fruitId \n" +
            "	left join SysDict bs on b.inclinationLevel  = bs.dvalue and bs.typeKey = 'InclinationsLevel' \n" +
            "WHERE\n" +
            "	a.isDel = '0' \n" +
            "	AND a.type = :type\n" +
            "	and b.inclinationLevel is not null\n" +
            "   and bor.authUserId = :userId " +
            "GROUP BY b.inclinationLevel")
    List<ResultPercentPO> getExpertDetailPsData(@Param("userId") Long userId, @Param("type") String type);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultPercentPO(bs.dname, count(a.publicationLevel) as num) \n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitPO a ON a.id = bor.fruitId " +
            "	left join SysDict bs on a.publicationLevel  = bs.dvalue and bs.typeKey = 'PubLevel'\n" +
            "WHERE\n" +
            "	a.isDel = '0' \n" +
            "	AND a.type = :type\n" +
            "	and a.publicationLevel is not null\n" +
            "   and bor.authUserId = :userId " +
            "GROUP BY a.publicationLevel")
    List<ResultPercentPO> getExpertDetailBzData(@Param("userId") Long userId, @Param("type") String type);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultPercentPO(bs.dname, count(a.journalType)) \n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitPO a ON a.id = bor.fruitId " +
            "	left join SysDict bs on a.journalType  = bs.dvalue and bs.typeKey = 'journalType'\n" +
            "WHERE\n" +
            "	a.isDel = '0' \n" +
            "	AND a.type = :type\n" +
            "	and a.journalType is not null\n" +
            "   and bor.authUserId = :userId " +
            "GROUP BY a.journalType")
    List<ResultPercentPO> getExpertDetailLwData(@Param("userId") Long userId, @Param("type") String type);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultPercentPO(bs.dname, count(a.reportType)) \n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitPO a ON a.id = bor.fruitId " +
            "	left join SysDict bs on a.reportType  = bs.dvalue and bs.typeKey = 'reportType'\n" +
            "WHERE\n" +
            "	a.isDel = '0' \n" +
            "	AND a.type = :type\n" +
            "	and a.reportType is not null\n" +
            "   and bor.authUserId = :userId " +
            "GROUP BY a.reportType")
    List<ResultPercentPO> getExpertDetailBgData(@Param("userId") Long userId, @Param("type") String type);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultPercentPO(bs.dname, count(a.clientLevel)) \n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitPO a ON a.id = bor.fruitId " +
            "	left join SysDict bs on a.clientLevel  = bs.dvalue and bs.typeKey = 'clientLevel'\n" +
            "WHERE\n" +
            "	a.isDel = '0' \n" +
            "	AND a.type = :type\n" +
            "	and a.clientLevel is not null\n" +
            "   and bor.authUserId = :userId " +
            "GROUP BY a.clientLevel")
    List<ResultPercentPO> getExpertDetailZcData(@Param("userId") Long userId, @Param("type") String type);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultPercentPO(bs.dname, count(a.bookType)) \n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitPO a ON a.id = bor.fruitId " +
            "	left join SysDict bs on a.bookType  = bs.dvalue and bs.typeKey = 'BookType'\n" +
            "WHERE\n" +
            "	a.isDel = '0' \n" +
            "	AND a.type = :type\n" +
            "	and a.bookType is not null\n" +
            "   and bor.authUserId = :userId " +
            "GROUP BY a.bookType")
    List<ResultPercentPO> getExpertDetailTsData(@Param("userId") Long userId, @Param("type") String type);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultLinePO(DATE_FORMAT(bf.pubTime, '%Y'), count(bf.pubTime))\n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitPO bf ON bf.id = bor.fruitId " +
            "	where DATE_FORMAT( bf.pubTime, '%Y' ) is not null " +
            "   and bor.authUserId = :userId " +
            "   and bf.isDel=0\n " +
            "GROUP BY\n" +
            "	DATE_FORMAT( bf.pubTime, '%Y' )")
    List<ResultLinePO> getFruitLineData(@Param("userId") Long userId);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultLinePO(DATE_FORMAT(bfl.inclinationTime, '%Y'), count(bfl.inclinationTime))\n" +
            "FROM\n" +
            "	FruitOrgRelPo bor \n" +
            "   LEFT JOIN FruitLeaderPO bfl ON bfl.fruitId = bor.fruitId\n" +
            "	where DATE_FORMAT( bfl.inclinationTime, '%Y' ) is not null " +
            "   and bor.authUserId = :userId " +
            "GROUP BY\n" +
            "	DATE_FORMAT( bfl.inclinationTime, '%Y')")
    List<ResultLinePO> getPsLineData(@Param("userId") Long userId);


    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultRelationPO(bfao.name, count(bfa.fruitId))" +
            "FROM\n" +
            "	FruitPO bf,\n" +
            "	FruitAuthorPO bfa,\n" +
            "	FruitAuthorOrgPO bfao \n" +
            "WHERE\n" +
            "	bfa.orgUserId = :userId\n" +
            "	and bfao.authorId = bfa.id\n" +
            "	and bf.id = bfa.fruitId\n" +
            "	and bf.isDel = '0'\n" +
            "GROUP BY\n" +
            "	bfao.name")
    List<ResultRelationPO> getRelationOrgData(@Param("userId") Long userId);

    @Query("SELECT\n" +
            "	new me.sdevil507.biz.fruit.po.ResultRelationPO(bou.realName, bori.photo, bori.company)\n" +
            "FROM\n" +
            "	FruitPO f\n" +
            "	LEFT JOIN FruitAuthorPO fa ON fa.fruitId = f.id\n" +
            "	LEFT JOIN OrgUserPo bou on bou.id = fa.orgUserId\n" +
            "	LEFT join OrgExpertInfoPo bori on bori.id = bou.expertInfo.id\n" +
            "WHERE\n" +
            "	f.id IN ( SELECT a.id FROM FruitPO a LEFT JOIN FruitAuthorPO b ON a.id = b.fruitId WHERE b.orgUserId = :userId and b.orgUserId is not NULL) \n" +
            "	AND fa.orgUserId != :userId \n" +
            "GROUP BY\n" +
            "	fa.orgUserId")
    List<ResultRelationPO> getRelationAuthorData(@Param("userId") Long userId);

    FruitPO findFruitPOByInitFruitId(Long initFruitId);
}
